import Foundation
import SQLite3

public class MPConvertibleDB {
    
    public static let `share` : MPConvertibleDB = MPConvertibleDB(name: "MPConvertibleDB")
        
    private let lock : NSLock = NSLock()
    
    private var db : OpaquePointer?
    
    init(name : String) {
        let fileURL = try? FileManager.default
            .url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
            .appendingPathComponent("\(name).sqlite")
        guard let url = fileURL else { return }
        do {
            try FileManager.default.createDirectory(at: url.deletingLastPathComponent(),
                                                    withIntermediateDirectories: true,
                                                    attributes: nil)
            sqlite3_open(url.path.cString(using: .utf8), &db)
        } catch {}
    }
    
    @discardableResult
    func createTable(_ sqlModel : MPConvertibleSqlModelInfo) -> Bool {
        lock.lock()
        guard let _db = db else {
            lock.unlock()
            return false
        }
        if sqlite3_exec(_db, sqlModel.tableSql, nil, nil, nil) == SQLITE_OK {
            lock.unlock()
            return true
        }
        lock.unlock()
        return false
    }
    
    @discardableResult
    func searchDataCount(_ sqlModel : MPConvertibleSqlModelInfo, params : [MPSqlSearchParam], limit : UInt = 0) -> Int {
        lock.lock()
        guard let _db = db else {
            lock.unlock()
            return 0
        }
        var sql = "SELECT COUNT(*) FROM \(sqlModel.tableName)"
        var paramList = params.filter { param in
            return param.value.count > 0
        }
        paramList.sort { c1, c2 in
            return c1.type.rawValue < c1.type.rawValue
        }
        if paramList.count > 0 {
            for (idx, param) in paramList.enumerated() {
                if idx == 0 {
                    sql.append(" WHERE")
                } else {
                    sql.append(param.type == .requir ? " AND" : " OR")
                }
                sql.append(" \(param.key)")
                switch param.opeartor {
                    case .equal:
                        sql.append(" =")
                    case .greater:
                        sql.append(" >")
                    case .less:
                        sql.append(" <")
                    case .greaterOrEqual:
                        sql.append(" >=")
                    case .lessOrEqual:
                        sql.append(" <=")
                    case .noEqual:
                        sql.append(" !=")
                    case .like:
                        sql.append(" LIKE")
                }
                sql.append(" '\(param.value)'")
            }
        }
        var stmt:OpaquePointer? = nil
        let prepare_result = sqlite3_prepare_v2(_db, sql.cString(using: .utf8), -1, &stmt, nil)
        if prepare_result != SQLITE_OK{
            sqlite3_finalize(stmt)
            lock.unlock()
            return 0
        }
        if sqlite3_step(stmt) == SQLITE_ROW {
            let count : Int = Int(sqlite3_column_int(stmt, 0))
            sqlite3_finalize(stmt)
            lock.unlock()
            return count
        }
        sqlite3_finalize(stmt)
        lock.unlock()
        return 0
    }
    
    @discardableResult
    func searchData(_ sqlModel : MPConvertibleSqlModelInfo, params : [MPSqlSearchParam], limit : UInt = 0) -> [[String : Any]]? {
        lock.lock()
        guard let _db = db else {
            lock.unlock()
            return nil
        }
        var sql = "SELECT * FROM \(sqlModel.tableName)"
        var paramList = params.filter { param in
            return param.value.count > 0
        }
        paramList.sort { c1, c2 in
            return c1.type.rawValue < c1.type.rawValue
        }
        if paramList.count > 0 {
            for (idx, param) in paramList.enumerated() {
                if idx == 0 {
                    sql.append(" WHERE")
                } else {
                    sql.append(param.type == .requir ? " AND" : " OR")
                }
                sql.append(" \(param.key)")
                switch param.opeartor {
                    case .equal:
                        sql.append(" =")
                    case .greater:
                        sql.append(" >")
                    case .less:
                        sql.append(" <")
                    case .greaterOrEqual:
                        sql.append(" >=")
                    case .lessOrEqual:
                        sql.append(" <=")
                    case .noEqual:
                        sql.append(" !=")
                    case .like:
                        sql.append(" LIKE")
                }
                sql.append(" '\(param.value)'")
            }
        }
        sql.append(" ORDER BY id ASC")
        if limit > 0 {
            sql.append(" LIMIT \(limit)")
        }
        var stmt:OpaquePointer? = nil
        let prepare_result = sqlite3_prepare_v2(_db, sql.cString(using: .utf8), -1, &stmt, nil)
        if prepare_result != SQLITE_OK{
            sqlite3_finalize(stmt)
            lock.unlock()
            return nil
        }
        var list : [[String : Any]] = []
        while sqlite3_step(stmt) == SQLITE_ROW {
            var map : [String : Any] = [:]
            for column in 0..<sqlite3_column_count(stmt) {
                if let columnName = sqlite3_column_name(stmt, column), let columnType = sqlite3_column_decltype(stmt, column) {
                    let name = String(cString: columnName)
                    switch MPConvertibleSqlType(rawValue: String(cString: columnType)) ?? .unknow {
                    case .mp_text:
                        if let pointer = sqlite3_column_text(stmt, column) {
                            let value = String.init(cString: pointer)
                            if let data = value.data(using: .utf8) {
                                map[name] = (try? JSONSerialization.jsonObject(with: data, options: [])) ?? value
                            } else {
                                map[name] = value
                            }
                        }
                    case .mp_integer:
                        if name == "id" {
                            map["mp_sql_id"] = sqlite3_column_int64(stmt, column)
                        } else {
                            map[name] = sqlite3_column_int64(stmt, column)
                        }
                    case .unknow:
                        break
                    case .mp_double:
                        map[name] = sqlite3_column_double(stmt, column)
                    }
                }
            }
            list.append(map)
        }
        sqlite3_finalize(stmt)
        lock.unlock()
        return list
    }
    
    private func searchCurrentId(_ sqlModel : MPConvertibleSqlModelInfo) -> Int64? {
        guard let _db = db else { return nil }
        let sql : String = "select max(id) from \(sqlModel.tableName)"
        var stmt:OpaquePointer? = nil
        let prepare_result = sqlite3_prepare_v2(_db, sql.cString(using: .utf8), -1, &stmt, nil)
        if prepare_result != SQLITE_OK{
            sqlite3_finalize(stmt)
            return nil
        }
        if sqlite3_step(stmt) == SQLITE_ROW {
            if sqlite3_column_count(stmt) > 0 {
                let sql_id = sqlite3_column_int64(stmt, 0)
                sqlite3_finalize(stmt)
                return sql_id
            }
        }
        sqlite3_finalize(stmt)
        return nil
    }
    
    @discardableResult
    func reloadTableInfo(_ tableName : String) -> [MPConvertibleSqlElemInfo] {
        lock.lock()
        guard let _db = db else {
            lock.unlock()
            return []
        }
        let sql = "PRAGMA table_info(\(tableName))"
        var stmt: OpaquePointer?
        if sqlite3_prepare_v2(_db, sql, -1, &stmt, nil) == SQLITE_OK {
            var elems : [MPConvertibleSqlElemInfo] = []
            while sqlite3_step(stmt) == SQLITE_ROW {
                let sqlIndex = sqlite3_column_int(stmt, 0)
                if sqlIndex > 0, let name = sqlite3_column_text(stmt, 1), let type = sqlite3_column_text(stmt, 2) {
                    let elemName : String = String(cString: name)
                    elems.append(MPConvertibleSqlElemInfo(sqlName: elemName, sqlType: MPConvertibleSqlType.init(rawValue: String(cString: type)) ?? .unknow, sqlIndex: sqlIndex, propertyInfo: MPConvertiblePropertyInfo.init(offset: 0, jsonKey: elemName, type: MPConvertibleString.self)))
                }
            }
            sqlite3_finalize(stmt)
            lock.unlock()
            return elems
        }
        sqlite3_finalize(stmt)
        lock.unlock()
        return []
    }
    
    @discardableResult
    func deleteData(_ sqlModel : MPConvertibleSqlModelInfo, sqlId : Int64) -> Bool {
        lock.lock()
        guard let _db = db else {
            lock.unlock()
            return false
        }
        let result : Int32 = sqlite3_exec(_db, "DELETE FROM \(sqlModel.tableName) WHERE id = \(sqlId)", nil, nil, nil)
        if result == SQLITE_OK {
            lock.unlock()
            return true
        }
        lock.unlock()
        return false
    }
    
    @discardableResult
    func resetTable(_ sqlModel : MPConvertibleSqlModelInfo) -> Bool {
        lock.lock()
        let result = self.execTransaction(["DROP TABLE IF EXISTS \(sqlModel.tableName)", sqlModel.tableSql])
        lock.unlock()
        return result
    }
    
    @discardableResult
    func updateData(_ sqlModel : MPConvertibleSqlModelInfo, model : MPConvertible, sql_id : String) -> Bool {
        lock.lock()
        guard let _db = db else {
            lock.unlock()
            return false
        }
        var stmt: OpaquePointer?
        let result : Int32 = sqlite3_prepare_v2(_db, sqlModel.updateSql.replacingOccurrences(of: "mp_sql_replace_id_key", with: "\(sql_id)"), -1, &stmt, nil)
        if result == SQLITE_OK {
            sqlite3_bind_int64(stmt, 0, Int64(sql_id) ?? 0)
            for elem in sqlModel.elems {
                let value = model.mp_value(for: elem.sqlName)
                switch elem.sqlType {
                case .mp_text:
                    if let str = valueToText(value, elem.propertyInfo) {
                        sqlite3_bind_text(stmt, elem.sqlIndex, str, -1, nil)
                    }
                case .mp_integer:
                    if let _value = value as? MPConvertibleIntValue {
                        sqlite3_bind_int64(stmt, elem.sqlIndex, (mp_number(_value, type: Int64.self) ?? 0) as! sqlite3_int64)
                    }
                    
                case .unknow:
                    break
                case .mp_double:
                    if let _value = value as? MPConvertibleFloatValue {
                        sqlite3_bind_double(stmt, elem.sqlIndex, (mp_number(_value, type: Double.self) ?? 0) as! Double)
                    }
                }
            }
            if sqlite3_step(stmt) == SQLITE_DONE {
                sqlite3_finalize(stmt)
                lock.unlock()
                return true
            } else {
                sqlite3_finalize(stmt)
                lock.unlock()
                return false
            }
        }
        sqlite3_finalize(stmt)
        lock.unlock()
        return false
    }
    
    @discardableResult
    func addData(_ sqlModel : MPConvertibleSqlModelInfo, model : MPConvertible) -> Int64? {
        lock.lock()
        guard let _db = db else {
            lock.unlock()
            return nil
        }
        var stmt: OpaquePointer?
        let result : Int32 = sqlite3_prepare_v2(_db, sqlModel.insertSql, -1, &stmt, nil)
        if result == SQLITE_OK {
            for elem in sqlModel.elems {
                let value = model.mp_value(for: elem.sqlName)
                switch elem.sqlType {
                case .mp_text:
                    if let str = valueToText(value, elem.propertyInfo) {
                        sqlite3_bind_text(stmt, elem.sqlIndex, str, -1, nil)
                    }
                case .mp_integer:
                    if let _value = value as? MPConvertibleNumber {
                        sqlite3_bind_int64(stmt, elem.sqlIndex, (mp_number(_value, type: Int64.self) ?? 0) as! sqlite3_int64)
                    }
                case .unknow:
                    break
                case .mp_double:
                    if let _value = value as? MPConvertibleFloatValue {
                        sqlite3_bind_double(stmt, elem.sqlIndex, (mp_number(_value, type: Double.self) ?? 0) as! Double)
                    }
                    if let _value = value as? MPConvertibleDate {
                        sqlite3_bind_double(stmt, elem.sqlIndex, (_value as? Date)?.timeIntervalSince1970 ?? 0)
                    }
                }
            }
            if sqlite3_step(stmt) == SQLITE_DONE {
                sqlite3_finalize(stmt)
                let sql_id = searchCurrentId(sqlModel)
                lock.unlock()
                return sql_id
            } else {
                sqlite3_finalize(stmt)
                lock.unlock()
                return nil
            }
        }
        sqlite3_finalize(stmt)
        lock.unlock()
        return nil
    }
    
    private func valueToText(_ value : Any?,_ propertyInfo : MPConvertiblePropertyInfo) ->UnsafePointer<CChar>? {
        if let str = value as? NSString {
            return str.utf8String
        }
        if let array = value as? [Any] {
            return (mp_arrayToStr(array) as? NSString ?? "").utf8String
        }
        if let dict = value as? [String : Any] {
            return (mp_dictToStr(dict) as? NSString ?? "").utf8String
        }
        return nil
    }
        
    @discardableResult
    func dropColumn(_ tableName : String, elems : [MPConvertibleSqlElemInfo]) -> Bool {
        lock.lock()
        var sqls : [String] = []
        for elem in elems {
            sqls.append("ALTER TABLE \(tableName) DROP COLUMN \(elem.sqlName)")
        }
        let result = execTransaction(sqls)
        lock.unlock()
        return result
    }
    
    @discardableResult
    func addColumn(_ tableName : String, elems : [MPConvertibleSqlElemInfo]) -> Bool {
        lock.lock()
        var sqls : [String] = []
        for elem in elems {
            sqls.append("ALTER TABLE \(tableName) ADD COLUMN \(elem.sqlName) \(elem.sqlType.rawValue)")
        }
        let result = execTransaction(sqls)
        lock.unlock()
        return result
    }
    
    func execTransaction(_ sqls: [String]) -> Bool {
        if sqls.count == 0 {
            return true
        }
        guard let _db = db else { return false }
        guard sqlite3_exec(_db, "BEGIN TRANSACTION;", nil, nil, nil) == SQLITE_OK else {
            return false
        }
        for sql in sqls {
            guard sqlite3_exec(_db, sql, nil, nil, nil) == SQLITE_OK else {
                sqlite3_exec(_db, "ROLLBACK TRANSACTION;", nil, nil, nil)
                return false
            }
        }
        guard sqlite3_exec(_db, "COMMIT TRANSACTION;", nil, nil, nil) == SQLITE_OK else {
            return false
        }
        return true
    }
    
    deinit {
        if let _db = db {
            sqlite3_close(_db)
        }
    }
}
